package abc.database;

import abc.base.BaseModel;
import abc.config.DatasourceConfigure;
import abc.database.interfaces.SqlHelperFactory;
import abc.model.DSInfo;
import abc.model.ElExpressionInfo;
import abc.model.PartInfo;
import abc.utils.JacksonUtil;
import abc.utils.ReflectionUtil;
import abc.utils.StringUtil;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import jakarta.annotation.PostConstruct;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Data
@Slf4j
@Component
@RefreshScope
public class ElExpressionQueryHelper {

    public ElExpressionQueryHelper() {
        init();
    }

    public ElExpressionQueryHelper(DatasourceConfigure datasourceConfigure) {
        this.datasourceConfigure = datasourceConfigure;
        init();
    }

    protected ThreadLocal<Map<String, ElExpressionInfo>> localElExprPatternMap = new ThreadLocal<>();

    /***
     * 格式：${1,2,3,4,5...}
     */
    private final Pattern MATCH_EL_IN = Pattern.compile("^\\s*\\$\\{(\\d+(,\\s*\\d+)*)\\}\\s*$");

    /***
     * 格式：${1-5, 7-9, ...}
     */
    private final Pattern MATCH_EL_REGION = Pattern.compile("^\\s*\\$\\{((\\d+\\-\\d+)(,\\s*\\d+\\-\\d+\\s*)*)\\}\\s*$");

    /***
     * 格式：${yyyy-MM-dd HH:mm:ss ~ yyyy-MM-dd HH:mm:ss, ...}
     */
    private final Pattern MATCH_DATETIME_REGION = Pattern.compile("^\\s*\\$\\{\\s*(((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\-(((0[13578]|1[02])\\-(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)\\-(0[1-9]|[12][0-9]|30))|(02\\-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))\\-02\\-29))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])\\s*\\~\\s*((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\-(((0[13578]|1[02])\\-(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)\\-(0[1-9]|[12][0-9]|30))|(02\\-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))\\-02\\-29))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9]))\\s*\\}$");

    /***
     * 格式：${yyyy/MM/dd HH:mm:ss - yyyy/MM/dd HH:mm:ss, ...}
     */
    private final Pattern MATCH_DATETIME2_REGION = Pattern.compile("^\\s*\\$\\{\\s*(((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\/(((0[13578]|1[02])\\/(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)\\/(0[1-9]|[12][0-9]|30))|(02\\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))\\/02\\/29))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])\\s*\\-\\s*((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\/(((0[13578]|1[02])\\/(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)\\/(0[1-9]|[12][0-9]|30))|(02\\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))\\/02\\/29))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9]))\\s*\\}$");

    private final Pattern MATCH_ADVANCE_SEARCH = Pattern.compile("\\$\\{\\s*(((\\w[\\w\\d_]*)\\s*( like |=|>|<|>=|<=)\\s*([^,\\{\\}]+))\\s*(,\\s*(\\w[\\w\\d_]*)\\s*( like |=|>|<|>=|<=)\\s*([^,\\{\\}]+))*)\\s*\\}");


    public final String $ADVANCE_SEARCH = "$ADVANCE_SEARCH";

    public final String $ORDERS = "$ORDERS";

    private List<Pattern> expressionPatterns = new ArrayList<>() {{
        add(MATCH_EL_IN);
        add(MATCH_EL_REGION);
        add(MATCH_DATETIME_REGION);
        add(MATCH_DATETIME2_REGION);
        add(MATCH_ADVANCE_SEARCH);
    }};

    @Value("${abc.api.keyword-search-enable:false}")
    protected Boolean enableKeywordSearch = false;

    @Value("${abc.api.keyword-splitter:}")
    protected String keywordSplitter;

    @Value("${abc.api.keyword-search-el-expression-open:false}")
    protected Boolean openKeywordElExpression;

    @Autowired
    protected DatasourceConfigure datasourceConfigure;

    @Autowired
    protected SqlHelperFactory sqlHelperFactory;

    protected Map<String, Map<String, Map<String, Object>>> tablesMap = new HashMap<>();

    protected Map<String, List<Map<String, Object>>> columnsMap = new HashMap<>();

    @PostConstruct
    public void init() {
        if(sqlHelperFactory == null) {
            sqlHelperFactory = new SqlHelperFactory();
        }
        if(datasourceConfigure == null ||
                datasourceConfigure.getDatasource() == null ||
                datasourceConfigure.getDatasource().entrySet().size()<1) {
            return;
        }
        datasourceConfigure.getDatasource().entrySet().forEach(config-> {
            try {
                log.info("DSInfo::::: " + JacksonUtil.toJson(config));
                DriverManagerDataSource dataSource = new DriverManagerDataSource();
                dataSource.setDriverClassName(config.getValue().getDriverClassName());
                dataSource.setUrl(config.getValue().getUrl());
                dataSource.setUsername(config.getValue().getUsername());
                dataSource.setPassword(config.getValue().getPassword());
                JdbcTemplate jdbcTemplate = new JdbcTemplate();
                jdbcTemplate.setDataSource(dataSource);

                List<String> list = StringUtil.splitString2List(dataSource.getUrl(), "/");
                list = StringUtil.splitString2List(list.get(list.size()-1), "?");
                String dbname = list.get(0);
                config.getValue().setName(dbname);
                tablesMap.put(dbname, getTableMap(config, jdbcTemplate, dbname));
                List<Map<String, Object>> columns = getColumns(config, jdbcTemplate, dbname);
                columnsMap.put(dbname, columns);
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                throw e;
            }
        });
    }

    /***
     * 是否存在字段名
     * @param dbname （库名）
     * @param table （表名）
     * @param column （字段名）
     * @return
     */
    public Boolean hasColumn(String dbname, String table, String column) {
        if(!columnsMap.containsKey(dbname)) {
            return false;
        }
        if(columnsMap.get(dbname)==null) {
            return false;
        }
        for (Map<String, Object> item : columnsMap.get(dbname)) {
            if(item == null) {
                continue;
            }
            if(!item.containsKey("annotationColumnName")) {
                continue;
            }
            if(item.get("annotationColumnName") == null) {
                continue;
            }
            if(!item.containsKey("table") || item.get("table")==null || !item.get("table").toString().equals(table)) {
                continue;
            }
            if(item.get("annotationColumnName").toString().equals(column) ||
                    StringUtil.getHumpName(item.get("annotationColumnName").toString(), true).equals(column)) {
                return true;
            }
        }
        return false;
    }


    /***
     * 是否存在表名
     * @param dbname （库名）
     * @param table （表名）
     * @return
     */
    public Boolean hasTable(String dbname, String table) {
        if(!columnsMap.containsKey(dbname)) {
            return false;
        }
        if(columnsMap.get(dbname)==null) {
            return false;
        }
        for (Map<String, Object> item : columnsMap.get(dbname)) {
            if(item == null) {
                continue;
            }
            if(!item.containsKey("annotationColumnName")) {
                continue;
            }
            if(item.get("annotationColumnName") == null) {
                continue;
            }
            if(!item.containsKey("table") || item.get("table")==null) {
                continue;
            }
            if(item.get("table").toString().equals(table) ||
                    StringUtil.getHumpName(item.get("table").toString(), true).equals(table)) {
                return true;
            }
        }
        return false;
    }

    public List<Map<String, Object>> getColumns(Map.Entry<String, DSInfo> config, JdbcTemplate jdbcTemplate, String dbname) {
        return sqlHelperFactory.getInstance(config.getValue()).getColumnList(dbname, jdbcTemplate);
    }

    public Map<String, Map<String, Object>> getTableMap(String dbname) {
        return this.tablesMap.get(dbname);
    }

    protected Map<String, Map<String, Object>> getTableMap(Map.Entry<String, DSInfo> config, JdbcTemplate jdbcTemplate, String dbname) {
        List<Map<String, Object>> list = sqlHelperFactory.getInstance(config.getValue()).getTableList(dbname, jdbcTemplate);
        Map<String, Map<String, Object>> tablesMap = new HashMap<>();
        list.forEach(item-> {
            if(!item.containsKey("table_name") ||
                    item.get("table_name")==null ||
                    StringUtil.isEmpty(item.get("table_name").toString())) {
                return;
            }
            String table = item.get("table_name").toString();
            tablesMap.put(table, item);
        });
        return tablesMap;
    }

    public QueryWrapper getQueryWrapperByElExpression(QueryWrapper queryWrapper, String fieldKey) {
        if(enableKeywordSearch && openKeywordElExpression && localElExprPatternMap.get()!=null &&
                localElExprPatternMap.get().containsKey(fieldKey)) {
            if(localElExprPatternMap.get().get(fieldKey).getPattern()
                    .matcher(localElExprPatternMap.get().get(fieldKey).getValue()).find()) {
                Matcher m = localElExprPatternMap.get().get(fieldKey).getPattern()
                        .matcher(localElExprPatternMap.get().get(fieldKey).getValue());
                if (m.find()) {
                    List<String> values = StringUtil.splitString2List(m.group(1), "\\,");
                    if (MATCH_EL_IN.equals(localElExprPatternMap.get().get(fieldKey))) {
                        queryWrapper = (QueryWrapper) queryWrapper.in(fieldKey, values);
                    } else if (MATCH_EL_REGION.equals(localElExprPatternMap.get().get(fieldKey).getPattern()) ||
                            MATCH_DATETIME_REGION.equals(localElExprPatternMap.get().get(fieldKey).getPattern()) ||
                            MATCH_DATETIME2_REGION.equals(localElExprPatternMap.get().get(fieldKey).getPattern())) {
                        queryWrapper = getBetweenWrapper(queryWrapper, localElExprPatternMap.get().get(fieldKey).getField(), values);
                    }
                }
            }
        }

        return queryWrapper;
    }
    protected QueryWrapper getSelectFieldQueryWrapper(QueryWrapper queryWarpper, String... selectField) {
        return queryWarpper.select(selectField);
    }

    public Wrapper getOrdersQueryWrapper(QueryWrapper queryWarpper, List<OrderItem> orders, String dbname, String table) {
        if(orders == null || orders.size()<1) {
            return getOrderDescByPkQueryWrapper(queryWarpper, dbname, table);
        }
        for(OrderItem orderItem : orders) {
            if(orderItem.isAsc()) {
                queryWarpper = (QueryWrapper) queryWarpper.orderByAsc(orderItem.getColumn());
            }
            else {
                queryWarpper = (QueryWrapper) queryWarpper.orderByDesc(orderItem.getColumn());
            }
        }
        return queryWarpper;
    }

    public Wrapper getOrderDescByPkQueryWrapper(QueryWrapper queryWarpper, String dbname, String table) {
        List<String> keys = getPrimaryKeyNames(dbname, table);
        return (Wrapper) queryWarpper.orderByDesc(keys);
    }

    protected Wrapper getOrderAscByPkQueryWrapper(QueryWrapper queryWarpper, String dbname, String table) {
        List<String> keys = getPrimaryKeyNames(dbname, table);
        return (Wrapper) queryWarpper.orderByAsc(keys);
    }

    public QueryWrapper getDefaultQueryWrapper(BaseModel model) {
        QueryWrapper queryWrapper = model.where();
        var fields = model.getClass().getDeclaredFields();
        for(var field : fields) {
            if(Modifier.isFinal(field.getModifiers())) {
                continue;
            }
            String fieldKey = field.getName();
            String fieldname = field.getName();
            TableField fieldAnn = field.getAnnotation(TableField.class);

            if(fieldAnn != null && StringUtil.isNotEmpty(fieldAnn.value())) {
                fieldname = fieldAnn.value();
            }
            if(!fieldAnn.exist()) {
                continue;
            }
            var value = ReflectionUtil.getFieldValue(model, field.getName());
            if(value == null) {
                if(localElExprPatternMap.get()!=null && localElExprPatternMap.get().containsKey(fieldKey)) {
                    localElExprPatternMap.get().get(fieldKey).setField(fieldname);
                    queryWrapper = getQueryWrapperByElExpression(queryWrapper, fieldKey);
                }
                else {
                    continue;
                }
            }
            else if(value instanceof String) {
                List<String> keywords = StringUtil.splitString2List(value.toString(), keywordSplitter);
                if(enableKeywordSearch && keywords.size()>1) {
                    AtomicInteger i= new AtomicInteger();
                    String finalFieldname = fieldname;
                    queryWrapper = (QueryWrapper) queryWrapper.and(wrapper-> keywords.stream().forEach(keyword-> {
                        ((QueryWrapper)wrapper).like(finalFieldname, keyword);
                        if(i.get()<keywords.size()-1) {
                            ((QueryWrapper) wrapper).or();
                        }
                        i.set(i.get()+1);
                    }));
                }
                else {
                    queryWrapper = (QueryWrapper) queryWrapper.like(fieldname, value);
                }
            }
            else {
                queryWrapper = (QueryWrapper) queryWrapper.eq(fieldname, value);
            }
        }

        if(localElExprPatternMap.get()!=null) {
            var patternList = localElExprPatternMap.get().entrySet().stream()
                    .filter(a-> Pattern.matches(String.format("\\%s(_\\d+)?",$ADVANCE_SEARCH), a.getKey()))
                    .map(b-> b.getValue())
                    .collect(Collectors.toList());

            for(ElExpressionInfo expr : patternList) {
                Matcher m = expr.getPattern().matcher(expr.getValue());
                if(!m.find()) {
                    continue;
                }
                String val = m.group(1);
                Matcher finalM = Pattern.compile("((\\w[\\w\\d_]*)\\s*( like |=|>|<|>=|<=)\\s*([^,\\{\\}]+))").matcher(val);

                List<PartInfo> list = new ArrayList<>();
                while (finalM.find()) {
                    list.add(new PartInfo() {{
                        setKey(finalM.group(2).trim());
                        setType(finalM.group(3).trim());
                        setValue(finalM.group(4).trim());
                    }});
                }

                queryWrapper.and(wrapper-> {
                    AtomicInteger i = new AtomicInteger();
                    for(PartInfo partInfo : list) {
                        i.getAndIncrement();
                        if(StringUtil.isEmpty(partInfo.getKey()) || StringUtil.isEmpty(partInfo.getValue()) || StringUtil.isEmpty(partInfo.getType())) {
                            continue;
                        }
                        Optional<Field> fieldOptional = Arrays.stream(fields).filter(f-> f.getName().equals(partInfo.getKey())).findFirst();
                        if(!fieldOptional.isPresent()) {
                            continue;
                        }
                        String field = fieldOptional.get().getName();
                        TableField fieldAnn = fieldOptional.get().getAnnotation(TableField.class);
                        if(fieldAnn == null || !fieldAnn.exist() || StringUtil.isEmpty(fieldAnn.value())) {
                            continue;
                        }
                        field = fieldAnn.value();
                        String finalField = field;

                        var w = (QueryWrapper) wrapper;
                        switch (partInfo.getType()) {
                            case "like":
                                w.like(finalField, partInfo.getValue());
                                break;
                            case "=":
                                w.eq(finalField, partInfo.getValue());
                                break;
                            case ">":
                                w.gt(finalField, partInfo.getValue());
                                break;
                            case "<":
                                w.lt(finalField, partInfo.getValue());
                                break;
                            case "<=":
                                w.le(finalField, partInfo.getValue());
                                break;
                            case ">=":
                                w.ge(finalField, partInfo.getValue());
                                break;
                        }
                        if(i.get() < list.size()) {
                            w.or();
                        }
                    }
                });
            }
            localElExprPatternMap.get().clear();
        }

        return queryWrapper;
    }

    private QueryWrapper getBetweenWrapper(QueryWrapper queryWrapper, String fieldname, List<String> values) {
        AtomicInteger i = new AtomicInteger();
        String finalFieldname = fieldname;
        queryWrapper = (QueryWrapper) queryWrapper.and(wrapper -> values.stream().forEach(keyword -> {
            List<String> valuesRegion = StringUtil.splitString2List(keyword, "~");
            if(valuesRegion.size() != 2) {
                valuesRegion = StringUtil.splitString2List(keyword, "-");
            }
            if(valuesRegion.size() != 2) {
                return;
            }
            ((QueryWrapper) wrapper).between(finalFieldname, valuesRegion.get(0), valuesRegion.get(1));
            if (i.get() < values.size() - 1) {
                ((QueryWrapper) wrapper).or();
            }
            i.set(i.get() + 1);
        }));
        return queryWrapper;
    }


    public List<String> getPrimaryKeyNames(String dbname, String table) {
        List<String> result = new ArrayList<>();
        if(!columnsMap.containsKey(dbname)) {
            return result;
        }
        List<Map<String, Object>> cols = columnsMap.get(dbname).stream().filter(a -> StringUtil.getHumpName(table, true).equals(StringUtil.getHumpName(a.get("table").toString(), true)) &&
                "1".equals(a.get("keyFlag").toString())).collect(Collectors.toList());
        if(cols != null && cols.size() > 0) {
            cols.forEach(a-> result.add(StringUtil.getHumpName(a.get("propertyName").toString(), true)));
        }

        return result;
    }

}
